﻿CREATE TABLE [dbo].[t_CreditInfo] (
    [BorrowerID]                         INT             NOT NULL,
    [DatePulled]                         SMALLDATETIME   NOT NULL,
    [ReportXML]                          NVARCHAR (MAX)  NULL,
    [ReportMerged]                       VARBINARY (MAX) NULL,
    [ScoreEfx]                           SMALLINT        NULL,
    [ScoreXpn]                           SMALLINT        NULL,
    [ScoreTU]                            SMALLINT        NULL,
    [ScoreTri]                           SMALLINT        NULL,
    [Chapter7_FileDate]                  SMALLDATETIME   NULL,
    [Chapter7_SatisfiedDate]             SMALLDATETIME   NULL,
    [Chapter7_Status]                    INT             NOT NULL,
    [Chapter7_Count]                     TINYINT         NULL,
    [Chapter13_FileDate]                 SMALLDATETIME   NULL,
    [Chapter13_SatisfiedDate]            SMALLDATETIME   NULL,
    [Chapter13_Status]                   INT             NOT NULL,
    [Chapter13_Count]                    TINYINT         NULL,
    [ForeclosureNOD_FileDate]            SMALLDATETIME   NULL,
    [ForeclosureNOD_SatisfiedDate]       SMALLDATETIME   NULL,
    [ForeclosureNOD_Status]              INT             NOT NULL,
    [ForeclosureNOD_Count]               TINYINT         NOT NULL,
    [BK_FC_NOD_Count]                    TINYINT         NULL,
    [DebtRatio]                          TINYINT         NULL,
    [CashReserveMos]                     TINYINT         NULL,
    [MortgageCount]                      TINYINT         NOT NULL,
    [MortgageMonthReviewedMax]           TINYINT         NULL,
    [MortgageSince]                      TINYINT         NOT NULL,
    [MortgageHistory_30]                 TINYINT         NOT NULL,
    [MortgageHistory_60]                 TINYINT         NOT NULL,
    [MortgageHistory_90]                 TINYINT         NOT NULL,
    [MortgageHistory_120]                TINYINT         NOT NULL,
    [TradeLinesTotal]                    TINYINT         NOT NULL,
    [TradeLinesCount_30]                 TINYINT         NOT NULL,
    [TradeLinesCount_60]                 TINYINT         NOT NULL,
    [TradeLinesCount_90]                 TINYINT         NOT NULL,
    [TradeLinesCount_120]                TINYINT         NOT NULL,
    [ChargeOffsCount]                    TINYINT         NOT NULL,
    [ChargeOffsAmount]                   INT             NULL,
    [TradeLinesCount_ActiveOpen_03]      TINYINT         NOT NULL,
    [TradeLinesCount_ActiveOpen_06]      TINYINT         NOT NULL,
    [TradeLinesCount_ActiveOpen_12]      TINYINT         NOT NULL,
    [TradeLinesCount_ActiveOpen_24]      TINYINT         NOT NULL,
    [TradeLinesCount_ActiveOpen_12_1000] TINYINT         NOT NULL,
    [TradeLinesCount_ActiveOpen_12_5000] TINYINT         NOT NULL,
    [TradeLinesCount_MR_24_5000]         TINYINT         NOT NULL,
    [PaymentDelayAverage]                INT             NOT NULL,
    [CreditReport]                       NVARCHAR (MAX)  NULL,
    [HumanReadable]                      NVARCHAR (MAX)  NULL,
    [IsaacScore]                         INT             NULL,
    [OpenBankruptcy]                     BIT             NULL,
    [PrevBankruptcy]                     TINYINT         NULL,
    [TimeInBureau]                       AS              (datediff(month,[TimeInBureauDate],getdate())),
    [TimeInBureauDate]                   DATETIME        NULL,
    [PayHistory]                         AS              (datediff(month,[PayHistoryDate],getdate())),
    [PayHistoryDate]                     DATETIME        NULL,
    [Pulled]                             BIT             NOT NULL,
    [BankruptcyDate]                     SMALLDATETIME   NULL,
    [DTEC]                               NVARCHAR (MAX)  NULL,
    [Counseling]                         BIT             NULL,
    [BK_7_Date]                          SMALLDATETIME   NULL,
    [BK_13_Date]                         SMALLDATETIME   NULL,
    [BK_Joint]                           BIT             NULL,
    [BK_7_Count]                         TINYINT         NULL,
    [BK_13_Count]                        TINYINT         NULL,
    [BK_7_Dismissed]                     BIT             NULL,
    [PulledEx]                           AS              ([dbo].[IsPulledEx]([HumanReadable])),
    [MortgageLate]                       BIT             NULL,
    [RepoCount]                          TINYINT         NULL,
    [OpenBK]                             BIT             NULL,
    [PhoneUtilityCollection]             BIT             NULL,
    [SSN_DL]                             BIT             NULL,
    [AddressMatches]                     BIT             NULL,
    [AnyWho]                             BIT             NULL,
    [DLSuspended]                        BIT             NULL,
    [CreditBureauID]                     TINYINT         NULL,
    [StatusID]                           INT             NOT NULL,
    [StatusDate]                         DATETIME        NOT NULL,
    [StatusUser]                         VARCHAR (32)    NOT NULL,
    [UpdateTest]                         DATETIME        NULL,
    [ID]                                 INT             IDENTITY (1, 1) NOT NULL
);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of tradelines', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesTotal';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'12 mos look back', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_30';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'12 mos look back', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_60';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'12 mos look back', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_90';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'12 mos look back', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_120';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'12 mos look back', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'ChargeOffsCount';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'24 mos back', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'ChargeOffsAmount';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of TL opened for >=3 months & active', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_ActiveOpen_03';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of TL opened for >=3 months', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_ActiveOpen_06';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of TL opened for >=3 months', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_ActiveOpen_12';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of TL opened for >=3 months', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_ActiveOpen_24';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of TL opened for >=12 months & ballance >= $1000', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_ActiveOpen_12_1000';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Number of TL opened for >=12 months & ballance >= $5000', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_ActiveOpen_12_5000';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'# TLs with MR >=12  & aggregate ballance >= $5000', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TradeLinesCount_MR_24_5000';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'In days', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'PaymentDelayAverage';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Date when first account was opened', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TimeInBureau';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Date when first account was opened', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'TimeInBureauDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Open date of first account in good standing', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'PayHistory';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Open date of first account in good standing', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'PayHistoryDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = NULL, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N't_CreditInfo', @level2type = N'COLUMN', @level2name = N'StatusDate';

